{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use Open Interpreter to talk to your database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Note: Open Interpreter should ideally be limited to read-only actions on your database. If write operations are necessary, use a copy of your data to protect against unexpected changes from the AI model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "plaintext"
    }
   },
   "outputs": [],
   "source": [
    "Set up a profile with:\n",
    "- Database credentials\n",
    "- Connection string\n",
    "\n",
    "Here is an example for a PostgreSQL database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from interpreter import interpreter\n",
    "import os\n",
    "\n",
    "# Use environment variables for database connection or update defaults with your credentials\n",
    "db_user = os.environ.get(\"DB_USER\", \"user\")\n",
    "db_host = os.environ.get(\"DB_HOST\", \"localhost\")\n",
    "db_port = os.environ.get(\"DB_PORT\", \"5432\")\n",
    "db_name = os.environ.get(\"DB_NAME\", \"demo_database\")\n",
    "db_password = os.environ.get(\"DB_PASSWORD\", \"\")\n",
    "\n",
    "# Construct connection string with optional password\n",
    "if db_password and db_password.strip():\n",
    "    connection_string = (\n",
    "        f\"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}\"\n",
    "    )\n",
    "else:\n",
    "    connection_string = f\"postgresql://{db_user}@{db_host}:{db_port}/{db_name}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Configure your instance of Open Interpreter.\n",
    "\n",
    "\n",
    "This example uses a local model served by Ollama but you can use a hosted model:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LLM settings\n",
    "interpreter.llm.model = \"ollama/llama3.1\"\n",
    "interpreter.llm.supports_functions = False\n",
    "interpreter.llm.execution_instructions = False\n",
    "interpreter.llm.max_tokens = 1000\n",
    "interpreter.llm.context_window = 7000\n",
    "interpreter.llm.load() \n",
    "\n",
    "# Computer settings\n",
    "interpreter.computer.import_computer_api = False\n",
    "\n",
    "# Misc settings\n",
    "interpreter.auto_run = False\n",
    "interpreter.offline = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the custom instructions to maximize performance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Custom Instructions\n",
    "interpreter.custom_instructions = f\"\"\"\n",
    "    You are a SQL master and are the oracle of database knowledge. You are obsessed with SQL. You only want to discuss SQL. SQL is life.\n",
    "    Recap the plan before answering the user's query.\n",
    "    You will connect to a PostgreSQL database, with the connection string {connection_string}.\n",
    "    Remember to only query the {db_name} database.\n",
    "    Execute valid SQL commands to satisfy the user's query.\n",
    "    Write all code in a full Python script. When you have to re-write code, redo the entire script.\n",
    "    Execute the script to get the answer for the user's query.\n",
    "    **YOU CAN EXECUTE SQL COMMANDS IN A PYTHON SCRIPT.***\n",
    "    Get the schema of '{db_name}' before writing any other SQL commands. It is important to know the tables. This will let you know what commands are correct.\n",
    "    Only use real column names.\n",
    "    ***You ARE fully capable of executing SQL commands.***\n",
    "    Be VERY clear about the answer to the user's query. They don't understand technical jargon so make it very clear and direct.\n",
    "    You should respond in a very concise way.\n",
    "    You can do it, I believe in you.\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Save the profile in the `profiles` directory.\n",
    "\n",
    "Once you are happy with your profile, test it on a test table/database. \n",
    "\n",
    "Run the following in your terminal:\n",
    "\n",
    "`interpreter --profile <name-of-profile.py>`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Iterate on the profile until you are happy with the performance. \n",
    "\n",
    "Remember to use the right LLM for the job. Larger models tend to have better reasoning.\n",
    "\n",
    "If you want to share your profile with the community, please open a PR."
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
